package com.shketai.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.shketai.entity.Classinfo;
import com.shketai.entity.Classroominfo;
import com.shketai.entity.Info;
import com.shketai.entity.Student;
import com.shketai.entity.Subject;
import com.shketai.util.DateStrUtil;

public class ClassinfoDao {

	private SessionFactory sessionFactory;

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	/**
	 * 新增班级
	 * 
	 * @param classinfo
	 * @param classroominfo_id
	 * @param subject_id
	 * @return
	 */
	public int add(Classinfo classinfo, int subject_id) {
		Session session = sessionFactory.getCurrentSession();
		Subject subject = new Subject();
		subject.setId(subject_id);
		classinfo.setSubject(subject);
		session.save(classinfo);
		return 1;
	}

	public int delete(Classinfo classinfo) {
		Session session = sessionFactory.getCurrentSession();
		session.delete(classinfo);
		return 1;
	}

	/**
	 * 教室排班
	 * 
	 * @param id
	 */
	public Classinfo findById(int id) {
		Session session = sessionFactory.getCurrentSession();
		Classinfo ci = (Classinfo) session.get(Classinfo.class, id);
		return ci;
	}

	public int update(Classinfo classinfo) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Classinfo where id=:id ");
		query.setInteger("id", classinfo.getId());
		List<Classinfo> list = query.list();
		Classinfo oldClassinfo = list.get(0);
		classinfo.setId(oldClassinfo.getId());
		classinfo.setClassroominfo(oldClassinfo.getClassroominfo());
		classinfo.setSubject(oldClassinfo.getSubject());
		session.clear();
		session.update(classinfo);
		return 1;
	}

	/**
	 * 选课时，当用户选择了某个科目，再查询符合年龄的班级
	 * 
	 * @param subjectId
	 *            课程编号
	 * @return age 用户的年龄
	 * @throws Exception
	 */
	public List<Classinfo> findBySubjectAndAge(int major_id, int stu_id,int page) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery(
				"from Classinfo where subject.majors.id = ? and min_age >= (select birthday from Student where id = ?)");
		query.setInteger(0, major_id);
		query.setInteger(1, stu_id);
		query.setFirstResult((page-1)*10);
		query.setMaxResults(10);
		List<Classinfo> list = query.list();
		return list;
	}
	
	public int findCount1(int major_id, int stu_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select count(*) from Classinfo where subject.majors.id = ? and min_age >= (select birthday from Student where id = ?)");
		query.setInteger(0, major_id);
		query.setInteger(1, stu_id);
		Long total = (Long) query.uniqueResult();
		return total.intValue();
	}


	/**
	 * 选课
	 * 
	 * @param uid
	 *    学生id
	 * @param classid
	 *     班级编号
	 */
	public void selectSubject(int stu_id, int classinfo_id) { 
		Session session = sessionFactory.getCurrentSession();

		Info info = new Info();

		// 学生对象
		Student stu = new Student();
		stu.setId(stu_id);
		info.setStudent(stu);

		// 查询班级对象
		Classinfo ci = (Classinfo) session.get(Classinfo.class, classinfo_id);
		if(ci.getNum()>0){
		// 该班级可选人数减1
		ci.setNum(ci.getNum() - 1);

		info.setClassinfo(ci);
		info.setFlag(1);
		info.setResults("已确认过报名须知！");
		session.save(info);
		}
	}

	public int getNum(int classinfo_id){
		int result;
		Session session = sessionFactory.getCurrentSession();
		Classinfo ci = (Classinfo) session.get(Classinfo.class, classinfo_id);
		if(ci.getNum()<=0){
			 return result = 0;
		}else{
			return result = 1;
		}
	}

	/**
	 * 查是否要面试
	 * @param classinfo_id
	 * @return
	 */
	public int findflag(int classinfo_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("select interview from subject ,classinfo  where subject.id = classinfo.subject_id and classinfo.id=?");
		query.setInteger(0, classinfo_id);
		int result =  (int) query.uniqueResult();
		return result;
	}
	/**
	 * 查询面试结果
	 * -1：没有面试，需要预约面试
	 * 1：面试不通过
	 * 2：通过
	 * @param stu_id
	 * @return
	 */
	public int findInterview_info(int stu_id){
		int result = -1;
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select result from Interview_info where student.id =?");
		query.setInteger(0, stu_id);
		//出现异常，首先看异常信息：158行空指针异常，也就是语句没有查询到结果，还要转成int型，就报空指针异常
		//43号学生没有面试，所以查不到，正常，所以你需要先判断，再转换
		Object object = query.uniqueResult();
		if(object != null){
			result =  (int) query.uniqueResult();
		}
		return result;
	
	}
	/**
	 * 退班
	 * 
	 * @param stu_id
	 *//*
	public void quitSubject(int id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("update Info set flag = 4 where id =?");
		query.setInteger(0, id);
		query.executeUpdate();
	}
*/
	public List<Classinfo> findAll(int page) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Session session = sessionFactory.getCurrentSession();
		SQLQuery sqlQuery = session.createSQLQuery(
				"SELECT t1.*,t3.subject,t4.`day`,t4.`start`,t4.jieshu,IFNULL(t2.count,0) as count from classinfo t1 LEFT JOIN (select classinfo_id,count(*) as count from info group by classinfo_id) t2 on t1.id = t2.classinfo_id LEFT JOIN subject t3 on t1.subject_id = t3.id LEFT JOIN classroominfo t4 ON t4.id = t1.classroominfo_id group by t1.id  ");
		sqlQuery.setFirstResult((page-1)*10);
		sqlQuery.setMaxResults(10);
		List<Object[]> list = sqlQuery.list();
		List<Classinfo> cis = new ArrayList<Classinfo>();
		for (Object[] array : list) {
			Classinfo ci = new Classinfo();
			ci.setId(Integer.parseInt(array[0].toString()));
			ci.setClassname(array[1].toString());
			ci.setKeshi(Integer.parseInt(array[2].toString()));
			ci.setMoney(Double.parseDouble(array[3].toString()));
			try {
				ci.setMin_age(sdf.parse(array[4].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			try {
				ci.setMax_age(sdf.parse(array[9].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			ci.setUnit_price(Double.parseDouble(array[7].toString()));
			ci.setNum(Integer.parseInt(array[8].toString()));
			Subject subject = new Subject();
			subject.setSubject(array[12].toString());
			ci.setSubject(subject);
			ci.setNumber(Integer.parseInt(array[11].toString()));
			ci.setCount(Integer.parseInt(array[16].toString()));
			Classroominfo classroominfo = new Classroominfo();
			classroominfo.setDay(Integer.parseInt(array[13].toString()));
			classroominfo.setStart(array[14].toString());
			classroominfo.setJieshu(array[15].toString());
			ci.setClassroominfo(classroominfo);
			cis.add(ci);
		}
//		Query query = session.createQuery("from Classinfo");

//		query.setFirstResult((page - 1) * 10);
//		query.setMaxResults(10);
//		List<Classinfo> list = query.list();
		return cis;
	}

	
	public List<Classinfo> findAll1() {
		//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//Session session = sessionFactory.getCurrentSession();
		//SQLQuery sqlQuery = session.createSQLQuery(
			//	"SELECT t1.*,t3.subject,count(*) as count from classinfo t1 LEFT JOIN info t2 on t1.id = t2.classinfo_id LEFT JOIN subject t3 on t1.subject_id = t3.id group by t1.id");
		//sqlQuery.setFirstResult((page - 1) * 10);
		//sqlQuery.setMaxResults(10);
		//List<Object[]> list = sqlQuery.list();
		//List<Classinfo> cis = new ArrayList<Classinfo>();
		/*for (Object[] array : list) {
			Classinfo ci = new Classinfo();
			ci.setId(Integer.parseInt(array[0].toString()));
			ci.setClassname(array[1].toString());
			ci.setKeshi(Integer.parseInt(array[2].toString()));
			ci.setMoney(Double.parseDouble(array[3].toString()));
			try {
				ci.setMin_age(sdf.parse(array[4].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			ci.setUnit_price(Double.parseDouble(array[7].toString()));
			Subject subject = new Subject();
			subject.setSubject(array[9].toString());
			ci.setSubject(subject);
			ci.setCount(Integer.parseInt(array[10].toString()));
			cis.add(ci);
		}
		return cis;*/
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery(" from Classinfo ");
		List<Classinfo> list = query.list();
		return list;
		
	}
	
	public int findRQ(int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select count(*) from Info where classinfo_id=:classinfo_id");
		query.setInteger("classinfo_id", classinfo_id);
		Long total = (Long) query.uniqueResult();
		return total.intValue();
	}

	/**
	 * 检测时间段是否有冲突
	 * 
	 * @param stu_id
	 * @param classinfo_id
	 * @return true有冲突 false 无冲突
	 */
	public boolean checkPeriod(int stu_id, int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery(
				"select classinfo.classroominfo.day,classinfo.classroominfo.start,classinfo.classroominfo.jieshu from"
						+ " Info where student.id=:sid");
		query.setInteger("sid", stu_id);
		List<Object[]> list = query.list();
		Query query1 = session.createQuery(
				"select classroominfo.day,classroominfo.start,classroominfo.jieshu from Classinfo where id=:id");
		query1.setInteger("id", classinfo_id);
		List<Object[]> list1 = query1.list();
		Object[] period = list1.get(0);
		boolean flag = false;
		for (Object[] array : list) {
			if (period[0].equals(array[0])) {
				if (DateStrUtil.compare(period[1].toString(), array[1].toString()) == 0) {
					flag = true;
					break;
				} else if (DateStrUtil.compare(period[1].toString(), array[1].toString()) < 0) {
					if (DateStrUtil.compare(period[2].toString(), array[1].toString()) > 0) {
						flag = true;
						break;
					}
				} else {
					if (DateStrUtil.compare(period[1].toString(), array[2].toString()) < 0) {
						flag = true;
						break;
					}
				}
			}
		}
		return flag;

	}

	/**
	 * 根据姓名查询班级
	 * 
	 * @param username
	 * @return
	 */ 
	public List<Classinfo> queryByUsername(String classname) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Session session = sessionFactory.getCurrentSession();
		SQLQuery sqlQuery = session.createSQLQuery(
				"SELECT t1.*,t3.subject,t4.`day`,t4.`start`,t4.jieshu,IFNULL(t2.count,0) as count from classinfo t1 LEFT JOIN (select classinfo_id,count(*) as count from info group by classinfo_id) t2 on t1.id = t2.classinfo_id LEFT JOIN subject t3 on t1.subject_id = t3.id LEFT JOIN classroominfo t4 ON t4.id = t1.classroominfo_id WHERE classname like ?  group by t1.id  desc");
		sqlQuery.setString(0, "%" + classname + "%").list();
		List<Object[]> list = sqlQuery.list();
		List<Classinfo> cis = new ArrayList<Classinfo>();
		for (Object[] array : list) {
			Classinfo ci = new Classinfo();
			ci.setId(Integer.parseInt(array[0].toString()));
			ci.setClassname(array[1].toString());
			ci.setKeshi(Integer.parseInt(array[2].toString()));
			ci.setMoney(Double.parseDouble(array[3].toString()));
			try {
				ci.setMin_age(sdf.parse(array[4].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			try {
				ci.setMax_age(sdf.parse(array[9].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			ci.setUnit_price(Double.parseDouble(array[7].toString()));
			ci.setNum(Integer.parseInt(array[8].toString()));
			Subject subject = new Subject();
			subject.setSubject(array[12].toString());
			ci.setSubject(subject);
			ci.setNumber(Integer.parseInt(array[11].toString()));
			ci.setCount(Integer.parseInt(array[16].toString()));
			Classroominfo classroominfo = new Classroominfo();
			classroominfo.setDay(Integer.parseInt(array[13].toString()));
			classroominfo.setStart(array[14].toString());
			classroominfo.setJieshu(array[15].toString());
			ci.setClassroominfo(classroominfo);
			cis.add(ci);
		}
		return cis;
	}

	public int findCount() {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select count(*) from Classinfo ");
		Long total = (Long) query.uniqueResult();
		return total.intValue();
	}

	public List<Classinfo> findBySubjectAndAges(int subject_id, int stu_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery(
				"from Classinfo where subject.id = ? and min_age <= (select birthday from Student where id = ?)");
		query.setInteger(0, subject_id);
		query.setInteger(1, stu_id);
		List<Classinfo> list = query.list();
		return list;
	}
	
	public List<Classinfo> findRoom(int classroom_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("SELECT c.classname,c2.`day`,c2.`start`,c2.jieshu from Classinfo c , classroominfo c2 where c.classroominfo_id = c2.id AND c2.classroom_id=?");
		query.setInteger(0, classroom_id);
		List<Object[]> list = query.list();
		List<Classinfo> cis = new ArrayList<Classinfo>();
		for (Object[] array : list) {
			Classinfo ci = new Classinfo();  
			ci.setClassname(array[0].toString());
			Classroominfo classroominfo = new Classroominfo();
			classroominfo.setDay(Integer.parseInt(array[1].toString()));
			classroominfo.setStart(array[2].toString());
			classroominfo.setJieshu(array[3].toString());
			ci.setClassroominfo(classroominfo);
			cis.add(ci);
		}
			return cis;
	}
}
